#use strict;
#use encoding 'utf-8';
use DBI;
#use CGI;
my $finyear ="2009-2010";

my $dbname=shift;
my $maindir = shift;
my $dbistring ="dbi:ODBC:$dbname";


my $dbh= DBI->connect ($dbistring,'','') || die "could not connect\n";

#
#
#

my $agency_query ="select exe_agency_code,agency_name  from exe_agency";
my $sth2_agency = $dbh->prepare($agency_query);
$sth2_agency->execute;
my %agencies;
my $count=0;
while (my @ar=$sth2_agency->fetchrow_array()) {
$count++;
my $x =$ar[1];
$x =~ s/\//\-/g;
$x =~ s/\./\-/g;
 $agencies{$ar[0]} = $x;
 
}
$count=0;
my $query1="select distinct Panchayats.district_code,Panchayats.block_code,Panchayats.panchayat_code,Districts.district_name,Blocks.block_name,Panchayats.panchayat_name from 
(Panchayats inner join Districts 
on districts.district_code=panchayats.district_code)
inner join blocks  
on blocks.block_code=panchayats.block_code";
my $sth=$dbh->prepare($query1);
$sth->execute();
my $district=0;
my $block=0;
my $panchayat=0;
my $count=0;
my %panchayats; #hash of hashes{ district_code=>{block_code=>(panchayat1,panchayat2,....),...},....}
my %districts;
my %blocks;
my %panchayatnames;
my %abstract;
while (my @ary=$sth->fetchrow_array ()) {
  push @{$panchayats{$ary[0]}{$ary[1]}},$ary[2];
  $blocks{$ary[1]}=$ary[4];
  $panchayatnames{$ary[2]}=$ary[5];
  $districts{$ary[0]} =$ary[3];
  $abstract{$ary[2]}{'no_of_workers'}=0;
  $abstract{$ary[2]}{'no_of_works'}=0;
  $abstract{$ary[2]}{'mandays'}=0;
}

my $query2="select bill_no,bill_date,material_code,rate,quantity,amount,bill_amt from UP40Material_purchased0809 where work_code=? and fin_year='2008-2009'";
my $sth2=$dbh->prepare($query2);
my $query3="SELECT UPWork_Sanction.Dt_Work_Start, UPWork_Sanction.Est_Cost_labour, UPWork_Sanction.Est_cost_material, UPWork_Sanction.Sanc_Tech_Labr_Skilled, UPWork_Sanction.Sanc_Tech_Labr_UnSkilled, UPWork_Sanction.Sanc_Tech_Labour, UPWork_Sanction.Sanc_Tech_Material, UPWork_Sanction.Sanc_Tech_Contingency, UPWork_Sanction.sanc_fin_Labour, UPWork_Sanction.sanc_fin_material, UPWork_Sanction.exp_Cash, UPWork_Sanction.Util_Amt_Labr_Skilled, UPWork_Sanction.Util_Amt_Labr_SemiSkilled, UPWork_Sanction.Util_Amt_Labr_Unskilled, UPWork_Sanction.Util_Amt_Material, UPWork_Sanction.Util_Amt_Contingency, UPWork_Sanction.UC_Status
FROM UPWork_Sanction where work_code=?";
my $sth3=$dbh->prepare($query3);
			
$count=1;
#$maindir="c:/nregaout";
my $query1="select distinct UPWork_detail.work_code,UPWork_detail.work_name from UPWork_detail 
where 
UPWork_detail.fin_year='2009-2010' and 
UPWork_detail.exe_agency_code=? and UPWork_detail.block_code=?";
my $query="SELECT    UP40MustRoll0910.reg_no, UP40MustRoll0910.applicant_name,UP40Registration.panchayat_code,UP40MustRoll0910.Tot_Attend,  
UP40MustRoll0910.amt_paid_measure,	UP40MustRoll0910.MSR_No
FROM (UP40Mustroll0910 INNER JOIN UP40Registration on (UP40MustRoll0910.reg_no=UP40Registration.reg_no)) 

WHERE   UP40MustRoll0910.work_code=? ";
#GROUP BY UP40Registration.panchayat_code,UP40MustRoll0809.MSR_No";
#INNER JOIN UPWork_detail on UPWork_detail.work_code=UP40MustRoll0809.work_code;
#and UPWork_detail.exe_agency_code=?
$sth=$dbh->prepare($query);

my $sth1=$dbh->prepare($query1);
#print %agencies;
foreach my $districtcode(keys %districts) {
foreach my $blockcode (%blocks) {
foreach my $key1(keys %agencies) {
#print "$key1 : agency code\n";
$sth1->execute($key1,$blockcode) || print "error\n";;
my @workcodes;
my %worknames;
while (my @arry=$sth1->fetchrow_array ()) {
#print @arry;
	if ($arry[0]!='') {push @workcodes,$arry[0];$worknames{$arry[0]}=$arry[1];} 
}
		print join(',',@workcodes);
		#exit;
		#break;


my %workers;


foreach my $workcode(@workcodes) {
	$sth->execute($workcode);
	while (my @arry=$sth->fetchrow_array()) {
		#$workcode,
		my $panchayat_code=$arry[2];
		my $reg_no=$arry[0];
		my $msrno=$arry[5];
		#$temparr=[$arry[3],$arry[4]];
		my $name=$arry[1];

		push @{$workers{$panchayat_code}{$workcode}{$reg_no}{$msrno}},$arry[3];
		push @{$workers{$panchayat_code}{$workcode}{$reg_no}{$msrno}},$arry[4];
		$workers{$panchayat_code}{$workcode}{$reg_no}{'name'}=$name;


	}
}

=head
print %workers;
use Data::Dumper;
print Dumper(%workers);
exit;
=cut
		$districtcode=($blockcode-$blockcode%1000)/1000;
		$district=$districts{$districtcode};
		#print $district,"\n";
		print "$maindir/$district/$blocks{$blockcode}";
		#exit;
		if (! -d "$maindir/$district") { mkdir "$maindir/$district";}
		#if (! -d "$maindir/$district/$blocks{$blockcode}") { mkdir "$maindir/$district/$blocks{$blockcode}";}
		if (! -d "$maindir/$district/$blocks{$blockcode}") { mkdir "$maindir/$district/$blocks{$blockcode}";}
		my $fh;
		open $fh,"> $maindir/$district/".$blocks{$blockcode}."/".$blocks{$blockcode}."_works_".$agencies{$key1}."_".$finyear.".html";
		binmode $fh ,":utf8";
		binmode STDOUT, ":utf8";
		#print $fh "Content-type:text/html;charset=utf-8;\n\n";
		#print $fh "Content-type: text/html;charset=utf-8";

		#('content="text/html; charset=utf-8"');
		print $fh qq(<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Work details</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="robots" content="index, follow" />

<link rel="stylesheet" href="../../styles.css" type="text/css" />
 </head>);
		#print $fh qq(<head> <link rel=stylesheet href="../../../style.css"></head>);
		print $fh qq(<body style="margin: 0px; padding: 0px;");
		print $fh qq(<h3 align=center> Works done in financial year $finyear  at $dbname by $agencies{$key1} </h3>);
		#print $fh qq(<h3 align=center> Name of Panchayat: $panchayatnames{$panchayat}</h3>);

		print $fh qq(<table  style="height: 100%;width:100%margin-top:40;" align=center cellpadding="10" cellspacing="0" border="1" >\n); # manipulte to look like you want it
 
		print $fh "<thead>";
		print $fh "<th>Sl.No.</th><th>Panchayat</th>";
		
		print $fh "<th>Name of Work/Worker details</th>";
		#print $fh "<th align=center> Panchayat wise work details</th>";
		print $fh "</thead>";
		$count=1;
my		$tot_lab=0;
my 		$tot_mat=0;
my 		$blockold="";
$lab_paid=0;
		foreach my $panchayat(sort keys %workers) {
my 					$panchayat_lab_tot=0;
my 					$panchayat_mat_tot=0;
my 					$blocknew=($panchayat-($panchayat%1000))/1000;
					
					print $fh "<tr>";
					print $fh "<td>";
					print $fh "  $count: ";
					print $fh "</td";
					print $fh "<td><font size=-3>";
					print $fh "  $panchayatnames{$panchayat} </font>";
					print $fh "</td>";
					#print $fh "</tr>";
					#print $fh "<tr>";
					print $fh "<td>";
					$count++;
					print $fh "<table>";		
					foreach my $workcode(keys %{$workers{$panchayat}}) {
my 						
						$l=0;
						print $fh "<tr>";
						print $fh "<td align=left><font size=-1>";
						print $fh " <b> $worknames{$workcode} <br> $workcode </b> ";
						#print $fh "<hr>";
						print $fh "</font></td>";
						print $fh "</tr><tr>";
						print $fh "<td><font size=-2>";
						$abstract{$panchayat}{'no_of_works'} +=1;
						$tdcount=0;
						#$abstract{panchayat}
						foreach my $worker(keys %{$workers{$panchayat}{$workcode}}) {
						$workercopy=$worker;
							if ($workercopy =~ /\/(.+)$/) {
								$workercopy=$1;
							}
						$abstract{$panchayat}{'no_of_workers'} +=1;
							print $fh "  $workers{$panchayat}{$workcode}{$worker}{'name'} [$workercopy]( ";
							$tdcount++;
							#if (($tdcount %9) =='8') {print $fh "<br>";}
							foreach my $mustroll(keys %{$workers{$panchayat}{$workcode}{$worker}}) {
								if ($mustroll ne 'name') {
									print $fh "$mustroll($workers{$panchayat}{$workcode}{$worker}{$mustroll}[0]),";
									$tdcount++;
							#		if (($tdcount %9) =='8') {print $fh "<br>";}
									$abstract{$panchayat}{'mandays'} +=$workers{$panchayat}{$workcode}{$worker}{$mustroll}[0];
									$lab_paid += $workers{$panchayat}{$workcode}{$worker}{$mustroll}[1];
								}
							}
							print $fh "),";
						}
							#print $fh "</font></td>";
							
							
							print $fh "<br>Labour=". $lab_paid." </font></td>";
							$panchayat_lab_paid+=$lab_paid;
							#$panchayat_mat_paid+=$mat_paid;
							$mat=$lab_paid=0;
							print $fh "</tr>";
							
					}
					
					print $fh "<tr><td colspan=2 align=center>Labour=$panchayat_lab_paid  </td></tr>";
					$tot_lab+=$panchayat_lab_paid;
					#$tot_mat+=$panchayat_mat_paid;
					$panchayat_lab_paid=$panchayat_mat_paid=0;
					print $fh "</table>";
					print $fh "</td>";
					print $fh "</tr>";
					$count++;
		}
		#print $fh "</table>";
		print $fh "<tr><td colspan=3 align=center>Labour=$tot_lab  </td></tr>";			
	print $fh "</table>";
		print $fh "</body></html>";
		close $fh;
		print $key1, "at the end\n";
		reset %workers;
		#exit;	
		}
		}
		}
		
